// © 2009 Microsoft Corporation.  All rights reserved.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Validation;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.InformationSecurity.ISRM.TaskManagment.Classes;
using Microsoft.InformationSecurity.ISRM.TaskManagment.TaskMessages;
using System.Data;
using System.Data.SqlClient;

namespace Microsoft.InformationSecurity.ISRM.TaskManagment.Classes
{
    /// <summary>
    /// Includes methods to add, update and delete tasks, history, notification schedules and templates.
    /// </summary>
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1053:StaticHolderTypesShouldNotHaveConstructors")]
    public class Tasks
    {
        /// <summary>
        /// Returns list of tasks for the busObjectID and specified page
        /// </summary>

        public static ICollection<Classes.Task> GetTasks(int businessObjectID)
        {
            List<Task> lstTasks = new List<Task>();
            Database db = null; IDataReader dbReader = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = db.ExecuteReader("ISRM_spGetTasks", businessObjectID, 0);
                while (dbReader.Read())
                {
                    Task t = new Task();
                    t.ID = dbReader.GetInt32(0);
                    t.Name = dbReader.GetString(1);
                    if (!dbReader.IsDBNull(2))
                        t.Description = dbReader.GetString(2);
                    t.Category = new Category();
                    t.Category.Name = dbReader.GetString(3);
                    t.Category.ID = dbReader.GetInt32(4);
                    t.Group = new Group();
                    t.Group.Name = dbReader.GetString(5);
                    t.Group.ID = dbReader.GetInt32(6);
                    t.AssignedTo = new User();
                    t.AssignedTo.Name = dbReader.GetString(7);
                    t.AssignedTo.ID = dbReader.GetInt32(8);
                    t.CreatedBy = dbReader.GetString(9);
                    t.CreatedDate = dbReader.GetDateTime(10);
                    t.DueDate = dbReader.GetDateTime(11);
                    t.Status = new TaskStatus();
                    t.Status.Name = dbReader.GetString(12);
                    t.Status.ID = dbReader.GetInt32(13);
                    t.HoursSpent = dbReader.GetInt32(14);
                    t.LastUpdatedBy = dbReader.GetString(15);
                    t.LastUpdatedDate = dbReader.GetDateTime(16);
                    t.BusinessObject = new BusinessObject();
                    t.BusinessObject.ID = dbReader.GetInt32(17);
                    t.BusinessObject.Name = dbReader.GetString(18);
                    t.NotificationSchedule = new NotificationSchedule();
                    t.NotificationSchedule.Name = dbReader.GetString(19);
                    t.NotificationSchedule.ID = dbReader.GetInt32(20);
                    lstTasks.Add(t);
                }
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return lstTasks;
        }

        /// <summary>
        /// Returns list of tasks for the specified user
        /// </summary>

        public static System.Collections.Generic.ICollection<Classes.Task> GetTasksByUser(int businessobjectid, int userID)
        {
            List<Task> lstTasks = new List<Task>();
            Database db = null; IDataReader dbReader = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = db.ExecuteReader("ISRM_spGetTasksByUser", businessobjectid, userID);
                while (dbReader.Read())
                {
                    Task t = new Task();
                    t.ID = dbReader.GetInt32(0);
                    t.Name = dbReader.GetString(1);
                    if (!dbReader.IsDBNull(2))
                        t.Description = dbReader.GetString(2);
                    t.Category = new Category();
                    t.Category.Name = dbReader.GetString(3);
                    t.Category.ID = dbReader.GetInt32(4);
                    t.Group = new Group();
                    t.Group.Name = dbReader.GetString(5);
                    t.Group.ID = dbReader.GetInt32(6);
                    t.AssignedTo = new User();
                    t.AssignedTo.Name = dbReader.GetString(7);
                    t.AssignedTo.ID = dbReader.GetInt32(8);
                    t.CreatedBy = dbReader.GetString(9);
                    t.CreatedDate = dbReader.GetDateTime(10);
                    t.DueDate = dbReader.GetDateTime(11);
                    t.Status = new TaskStatus();
                    t.Status.Name = dbReader.GetString(12);
                    t.Status.ID = dbReader.GetInt32(13);
                    t.HoursSpent = dbReader.GetInt32(14);
                    t.LastUpdatedBy = dbReader.GetString(15);
                    t.LastUpdatedDate = dbReader.GetDateTime(16);
                    t.BusinessObject = new BusinessObject();
                    t.BusinessObject.ID = dbReader.GetInt32(17);
                    t.BusinessObject.Name = dbReader.GetString(18);
                    t.NotificationSchedule = new NotificationSchedule();
                    t.NotificationSchedule.Name = dbReader.GetString(19);
                    t.NotificationSchedule.ID = dbReader.GetInt32(20);
                    lstTasks.Add(t);
                }
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return lstTasks;
        }



        /// <summary>
        /// Creates multiple tasks and returns the task identifiers
        /// </summary>
        /// <param name="lstTasks">List of new tasks</param>
        /// <returns>List of tasks with identifiers</returns>
        public static ICollection<Task> AddTasks(ICollection<Task> lstTasks)
        {
            if (lstTasks != null && lstTasks.Count > 0)
            {

                Database db = null;
                try
                {
                    db = DatabaseFactory.CreateDatabase("ISRMDB");
                    foreach (Task task in lstTasks)
                    {

                        SqlCommand createCatCommand = new SqlCommand("ISRM_spInsertTask");
                        createCatCommand.CommandType = CommandType.StoredProcedure;
                        createCatCommand.Parameters.Add("@title", SqlDbType.NVarChar, 255);
                        createCatCommand.Parameters.Add("@description", SqlDbType.NVarChar);
                        createCatCommand.Parameters.Add("@categoryID", SqlDbType.Int);
                        createCatCommand.Parameters.Add("@groupID", SqlDbType.Int);
                        createCatCommand.Parameters.Add("@assignedToID", SqlDbType.Int);
                        createCatCommand.Parameters.Add("@dueDate", SqlDbType.DateTime);
                        createCatCommand.Parameters.Add("@statusID", SqlDbType.Int);
                        createCatCommand.Parameters.Add("@hoursSpent", SqlDbType.Int);
                        createCatCommand.Parameters.Add("@createdBy", SqlDbType.NVarChar, 50);
                        createCatCommand.Parameters.Add("@lastUpdatedBy", SqlDbType.NVarChar, 50);
                        createCatCommand.Parameters.Add("@notificationScheduleID", SqlDbType.Int);
                        createCatCommand.Parameters.Add("@businessobjectID", SqlDbType.Int);
                        createCatCommand.Parameters.Add("@taskID", SqlDbType.Int);
                        createCatCommand.Parameters["@title"].Value = task.Name;
                        createCatCommand.Parameters["@description"].Value = task.Description;
                        createCatCommand.Parameters["@categoryID"].Value = task.Category.ID;
                        createCatCommand.Parameters["@groupID"].Value = task.Group.ID;
                        createCatCommand.Parameters["@statusID"].Value = task.Status.ID;
                        createCatCommand.Parameters["@assignedToID"].Value = task.AssignedTo.ID;
                        createCatCommand.Parameters["@dueDate"].Value = task.DueDate;
                        createCatCommand.Parameters["@hoursSpent"].Value = task.HoursSpent;
                        createCatCommand.Parameters["@createdBy"].Value = task.LastUpdatedBy;
                        createCatCommand.Parameters["@lastUpdatedBy"].Value = task.CreatedBy;
                        createCatCommand.Parameters["@notificationScheduleID"].Value = task.NotificationSchedule.ID;
                        createCatCommand.Parameters["@businessobjectID"].Value = task.BusinessObject.ID;
                        createCatCommand.Parameters["@taskID"].Direction = ParameterDirection.Output;
                        createCatCommand.Parameters.Add("@personID", SqlDbType.Int).Value = task.PersonID;

                        db.ExecuteNonQuery(createCatCommand);
                        task.ID = (int)createCatCommand.Parameters["@taskID"].Value;

                    }
                }
                catch (SqlException sqlEx)
                {
                    string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                    if (!string.IsNullOrEmpty(dbErrorMessage))
                    {
                        throw new ArgumentException(dbErrorMessage);
                    }
                    else
                        throw;

                }
                catch { throw; }
                finally
                {
                    if (db != null)
                        db = null;
                }



            }
            return lstTasks;
        }

        /// <summary>
        /// Updates a specific task
        /// </summary>
        public static void UpdateTask(Task task)
        {
            if (task == null)
                throw new ArgumentNullException("task");

            if (string.IsNullOrEmpty(task.Name))
                throw new ArgumentNullException("Task Name", TaskMessages.Messages.GetServiceMessage("TaskNameNotNull"));

            ValidationResults validationResults = Validation.Validate<Task>(task, "TaskBasicRules");

            if (validationResults.IsValid == false)
            {
                throw new ArgumentException(TaskMessages.Messages.GetServiceMessage("TaskValidationFailed"));
            }

            Database db = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                SqlCommand createCatCommand = new SqlCommand("ISRM_spUpdateTask");
                createCatCommand.CommandType = CommandType.StoredProcedure;
                createCatCommand.Parameters.Add("@taskID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@title", SqlDbType.NVarChar, 255);
                createCatCommand.Parameters.Add("@description", SqlDbType.NVarChar);
                createCatCommand.Parameters.Add("@categoryID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@groupID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@statusID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@assignedToID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@dueDate", SqlDbType.DateTime);
                createCatCommand.Parameters.Add("@hoursSpent", SqlDbType.Int);
                createCatCommand.Parameters.Add("@lastUpdatedBy", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@notificationScheduleID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@businessObjectID", SqlDbType.Int);

                createCatCommand.Parameters["@taskID"].Value = task.ID;
                createCatCommand.Parameters["@title"].Value = task.Name;
                createCatCommand.Parameters["@description"].Value = task.Description;
                createCatCommand.Parameters["@categoryID"].Value = task.Category.ID;
                createCatCommand.Parameters["@groupID"].Value = task.Group.ID;
                createCatCommand.Parameters["@statusID"].Value = task.Status.ID;
                createCatCommand.Parameters["@assignedToID"].Value = task.AssignedTo.ID;
                createCatCommand.Parameters["@dueDate"].Value = task.DueDate;
                createCatCommand.Parameters["@hoursSpent"].Value = task.HoursSpent;
                createCatCommand.Parameters["@lastUpdatedBy"].Value = task.LastUpdatedBy;
                createCatCommand.Parameters["@notificationScheduleID"].Value = task.NotificationSchedule.ID;
                createCatCommand.Parameters["@businessObjectID"].Value = task.BusinessObject.ID;
                createCatCommand.Parameters.Add("@personID", SqlDbType.Int).Value = task.PersonID;

                db.ExecuteNonQuery(createCatCommand);

            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
        }


        /// <summary>
        /// Returns task for the specified identifier
        /// </summary>
        public static Task GetTask(int taskID)
        {
            Task t = null;
            Database db = null; IDataReader dbReader = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = db.ExecuteReader("ISRM_spGetTask", taskID);
                while (dbReader.Read())
                {
                    t = new Task();
                    t.ID = dbReader.GetInt32(0);
                    t.Name = dbReader.GetString(1);
                    if (!dbReader.IsDBNull(2))
                        t.Description = dbReader.GetString(2);
                    t.Category = new Category();
                    t.Category.Name = dbReader.GetString(3);
                    t.Category.ID = dbReader.GetInt32(4);
                    t.Group = new Group();
                    t.Group.Name = dbReader.GetString(5);
                    t.Group.ID = dbReader.GetInt32(6);
                    t.AssignedTo = new User();
                    t.AssignedTo.Name = dbReader.GetString(7);
                    t.AssignedTo.ID = dbReader.GetInt32(8);
                    t.CreatedBy = dbReader.GetString(9);
                    t.CreatedDate = dbReader.GetDateTime(10);
                    t.DueDate = dbReader.GetDateTime(11);
                    t.Status = new TaskStatus();
                    t.Status.Name = dbReader.GetString(12);
                    t.Status.ID = dbReader.GetInt32(13);
                    t.HoursSpent = dbReader.GetInt32(14);
                    t.LastUpdatedBy = dbReader.GetString(15);
                    t.LastUpdatedDate = dbReader.GetDateTime(16);
                    t.BusinessObject = new BusinessObject();
                    t.BusinessObject.ID = dbReader.GetInt32(17);
                    t.BusinessObject.Name = dbReader.GetString(18);
                    t.NotificationSchedule = new NotificationSchedule();
                    t.NotificationSchedule.Name = dbReader.GetString(19);
                    t.NotificationSchedule.ID = dbReader.GetInt32(20);
                }
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return t;
        }

        /// <summary>
        /// Returns the list of task status
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1002:DoNotExposeGenericLists")]
        public static List<TaskStatus> GetAllTaskStatus()
        {
            Database db = null;
            List<TaskStatus> lstStatus = new List<TaskStatus>();
            IDataReader dbReader = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = (SqlDataReader)db.ExecuteReader(CommandType.StoredProcedure, "ISRM_spGetAllTaskStatus");
                while (dbReader.Read())
                {
                    TaskStatus ts = new TaskStatus();
                    ts.ID = dbReader.GetInt32(0);
                    ts.Name = dbReader.GetString(1);
                    ts.TaskCount = dbReader.GetInt32(2);
                    lstStatus.Add(ts);
                }
            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return lstStatus;
        }

        /// <summary>
        /// Returns the list of users in the system
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1002:DoNotExposeGenericLists")]
        public static List<User> GetTasksUsers()
        {
            Database db = null;
            List<User> lstUsers = new List<User>(); IDataReader dbReader = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = (SqlDataReader)db.ExecuteReader(CommandType.StoredProcedure, "ISRM_spGetTasksUsers");
                while (dbReader.Read())
                {
                    User user = new User();
                    user.ID = dbReader.GetInt32(0);
                    user.Name = dbReader.GetString(1);
                    lstUsers.Add(user);
                }
            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return lstUsers;
        }

        /// <summary>
        /// Returns number of tasks for the specified filter.
        /// </summary>
        /// <param name="field">Name of the filter field.</param>
        /// <param name="soperator">Operator for the filter.</param>
        /// <param name="values">Values for the filter.</param>
        /// <param name="isActiveTasksOnly">True to return only active tasks</param>
        /// <param name="isTaskUser">True if the user is a task user</param>
        /// <param name="personID">User id of the task user</param>
        /// <returns></returns>
        public static int GetTasksCountByFilter(string field, string soperator, string[] values, bool isTaskUser, int personID, bool isActiveTasksOnly)
        {
            Database db = null;
            int taskCount = 0;

            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                SqlCommand createCatCommand = new SqlCommand("ISRM_spGetTasksByFilterCount");
                createCatCommand.CommandType = CommandType.StoredProcedure;
                createCatCommand.Parameters.Add("@fieldName", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@fieldOperator", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@taskCount", SqlDbType.Int);
                createCatCommand.Parameters.Add("@isTaskUser", SqlDbType.Bit);
                createCatCommand.Parameters.Add("@personID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@isActive", SqlDbType.Bit);
                createCatCommand.Parameters.Add("@value1", SqlDbType.NVarChar, 255);
                createCatCommand.Parameters.Add("@value2", SqlDbType.NVarChar, 255);
                createCatCommand.Parameters["@fieldName"].Value = field;
                createCatCommand.Parameters["@fieldOperator"].Value = soperator;
                createCatCommand.Parameters["@taskCount"].Direction = ParameterDirection.Output;
                createCatCommand.Parameters["@isTaskUser"].Value = isTaskUser;
                createCatCommand.Parameters["@isActive"].Value = isActiveTasksOnly;
                createCatCommand.Parameters["@personID"].Value = personID;
                if (values != null && values.Length >= 1)
                    createCatCommand.Parameters["@value1"].Value = values[0];
                else
                    createCatCommand.Parameters["@value1"].Value = "";
                if (values != null && values.Length >= 2)
                    createCatCommand.Parameters["@value2"].Value = values[1];
                db.ExecuteNonQuery(createCatCommand);
                taskCount = (int)createCatCommand.Parameters["@taskCount"].Value;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
            return taskCount;
        }

        /// <summary>
        /// Returns the list of tasks for the specified filter.
        /// </summary>
        /// <param name="field">Name of the filter field</param>
        /// <param name="soperator">Operator of the filter</param>
        /// <param name="values">Values of the filter</param>
        /// <param name="sortField">Sort Field</param>
        /// <param name="sortDescending">Boolean to sort Descending</param>
        /// <param name="pageSize">Size of the page</param>
        /// <param name="startRowIndex">Starting row index</param>
        /// <param name="isActiveTasksOnly">True to return only active tasks</param>
        /// <param name="isTaskUser">True if the user is just a task user</param>
        /// <param name="personID">User id</param>
        /// <returns></returns>

        public static ICollection<Task> GetTasksByFilter(string field, string soperator, string[] values, string sortField, bool sortDescending, int pageSize, int startRowIndex, bool isTaskUser, int personID, bool isActiveTasksOnly)
        {
            Database db = null;
            List<Task> lstTasks = new List<Task>(); IDataReader dbReader = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                SqlCommand createCatCommand = new SqlCommand("ISRM_spGetTasksByFilter");
                createCatCommand.CommandType = CommandType.StoredProcedure;
                createCatCommand.Parameters.Add("@fieldName", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@fieldOperator", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@sortField", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@sortDescending", SqlDbType.Bit);
                createCatCommand.Parameters.Add("@pageSize", SqlDbType.Int);
                createCatCommand.Parameters.Add("@startRowIndex", SqlDbType.Int);
                createCatCommand.Parameters.Add("@isTaskUser", SqlDbType.Bit);
                createCatCommand.Parameters.Add("@personID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@isActive", SqlDbType.Bit);
                createCatCommand.Parameters.Add("@value1", SqlDbType.NVarChar, 255);
                createCatCommand.Parameters.Add("@value2", SqlDbType.NVarChar, 255);
                createCatCommand.Parameters["@fieldName"].Value = field;
                createCatCommand.Parameters["@fieldOperator"].Value = soperator;
                createCatCommand.Parameters["@sortField"].Value = sortField;
                createCatCommand.Parameters["@sortDescending"].Value = sortDescending;
                createCatCommand.Parameters["@pageSize"].Value = pageSize;
                createCatCommand.Parameters["@startRowIndex"].Value = startRowIndex;
                createCatCommand.Parameters["@isTaskUser"].Value = isTaskUser;
                createCatCommand.Parameters["@isActive"].Value = isActiveTasksOnly;
                createCatCommand.Parameters["@personID"].Value = personID;
                if (values != null && values.Length >= 1)
                    createCatCommand.Parameters["@value1"].Value = values[0];
                else
                    createCatCommand.Parameters["@value1"].Value = "";
                if (values != null && values.Length >= 2)
                    createCatCommand.Parameters["@value2"].Value = values[1];
                dbReader = (SqlDataReader)db.ExecuteReader(createCatCommand);
                while (dbReader.Read())
                {
                    Task t = new Task();
                    t.ID = dbReader.GetInt32(1);
                    t.Name = dbReader.GetString(2);
                    t.Category = new Category();
                    t.Category.Name = dbReader.GetString(3);
                    t.Category.ID = dbReader.GetInt32(4);
                    t.Group = new Group();
                    t.Group.Name = dbReader.GetString(5);
                    t.Group.ID = dbReader.GetInt32(6);
                    t.AssignedTo = new User();
                    t.AssignedTo.Name = dbReader.GetString(7);
                    t.AssignedTo.ID = dbReader.GetInt32(8);
                    t.CreatedBy = dbReader.GetString(9);
                    t.CreatedDate = dbReader.GetDateTime(10);
                    t.DueDate = dbReader.GetDateTime(11);
                    t.Status = new TaskStatus();
                    t.Status.Name = dbReader.GetString(12);
                    t.Status.ID = dbReader.GetInt32(13);
                    t.LastUpdatedBy = dbReader.GetString(14);
                    t.LastUpdatedDate = dbReader.GetDateTime(15);
                    t.BusinessObject = new BusinessObject();
                    t.BusinessObject.ID = dbReader.GetInt32(16);
                    t.BusinessObject.Name = dbReader.GetString(17);
                    lstTasks.Add(t);
                }

            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return lstTasks;
        }

        /// <summary>
        /// Returns tasks for the specified user and comma seperated notification ID's.
        /// </summary>
        /// <param name="userID">User Id</param>
        /// <param name="notificationIDs">Comma Separated Notification ID's</param>
        /// <returns>List of Tasks</returns>

        public static ICollection<Task> GetTasksByNotification(int userID, string notificationIDs)
        {
            Database db = null;
            List<Task> lstTasks = new List<Task>(); IDataReader dbReader = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = (SqlDataReader)db.ExecuteReader("ISRM_spGetTasksByNotification", userID, notificationIDs);
                while (dbReader.Read())
                {
                    Task t = new Task();
                    t.ID = dbReader.GetInt32(0);
                    t.Name = dbReader.GetString(1);
                    if (!dbReader.IsDBNull(2))
                        t.Description = dbReader.GetString(2);
                    t.Category = new Category();
                    t.Category.Name = dbReader.GetString(3);
                    t.Category.ID = dbReader.GetInt32(4);
                    t.Group = new Group();
                    t.Group.Name = dbReader.GetString(5);
                    t.Group.ID = dbReader.GetInt32(6);
                    t.AssignedTo = new User();
                    t.AssignedTo.Name = dbReader.GetString(7);
                    t.AssignedTo.ID = dbReader.GetInt32(8);
                    t.CreatedBy = dbReader.GetString(9);
                    t.CreatedDate = dbReader.GetDateTime(10);
                    t.DueDate = dbReader.GetDateTime(11);
                    t.Status = new TaskStatus();
                    t.Status.Name = dbReader.GetString(12);
                    t.Status.ID = dbReader.GetInt32(13);
                    t.HoursSpent = dbReader.GetInt32(14);
                    t.LastUpdatedBy = dbReader.GetString(15);
                    t.LastUpdatedDate = dbReader.GetDateTime(16);
                    t.BusinessObject = new BusinessObject();
                    t.BusinessObject.ID = dbReader.GetInt32(17);
                    t.BusinessObject.Name = dbReader.GetString(18);
                    t.NotificationSchedule = new NotificationSchedule();
                    t.NotificationSchedule.Name = dbReader.GetString(19);
                    t.NotificationSchedule.ID = dbReader.GetInt32(20);
                    lstTasks.Add(t);
                }

            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return lstTasks;
        }

        /// <summary>
        /// Returns the user details for the specified user id.
        /// </summary>
        /// <param name="userID">User ID</param>
        /// <returns>User Details</returns>
        public static User GetUser(int userID)
        {
            Database db = null;
            IDataReader dbReader = null;
            User u = new User();
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = (SqlDataReader)db.ExecuteReader("ISRM_spGetUserDetails", userID);
                while (dbReader.Read())
                {
                    u.ID = dbReader.GetInt32(0);
                    u.Name = dbReader.GetString(1);
                    u.LoginName = dbReader.GetString(2);
                    u.EmailAddress = dbReader.GetString(3);
                }

            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return u;
        }

        /// <summary>
        /// Returns true if the user is assigned atleast one task for the risk which the task belongs to.
        /// </summary>
        public static bool IsValidTrustedTaskUser(int taskID, int userID)
        {
            Database db = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                SqlCommand createNotRecCommand = new SqlCommand("ISRM_spIsValidTrustedTaskUser");
                createNotRecCommand.CommandType = CommandType.StoredProcedure;
                object isValid = db.ExecuteScalar("ISRM_spIsValidTrustedTaskUser", taskID, userID);
                if (isValid != null)
                    if ((int)isValid > 0)
                        return true;
                    else
                        return false;
                else
                    return false;
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
        }
    }
}
